#! /usr/bin/python
# -*- coding: cp1250 -*-

import re, string, sys, glob, os, time, shutil
import pepe.p_utils.txt2pg as ppt
import pepe.p_sql.pg as pg
import pepe.p_oslevel.basic as posb

#print posb.wai()

def listTables():
    ''' '''
    res = []
    q = """select * from pg_tables where schemaname='public'"""
    for r in pg.Pg(q):
        res.append(r[1])
    return res

def dropTable(t):
    ''' '''
    #pg.Pg("""drop table %s""" % (t,))
    try:
        pg.Pg("drop table %s" % (t,))
        return 1
    except:
        return 0

def loadData(fMask='', cDelimiter=''):
    ''' '''
    files = glob.glob(fMask)
    for f in files:
        # table name
        t = f[:-4]
        # drop table if exists
        if t in listTables(): dropTable(t)

        p = ppt.TxtLoad()
        p.initFile(       inputFile=f
                        , colDelimiter=cDelimiter
                        , hasHeader=0
                        )
        p.dropTableBefore = 0
        #p.pgClientEncoding = 'UTF8'
        #p.pgClientEncoding = 'cp1250'
        p.createTable()
        p.loadData()

def joinPerKeyPg(fMask='', cDelimiter='', debugmode=False):
    '''
    /* Compare two big files in postgresql
     * 
     * joined by key, anything else is just copied
     * key is to be assumed in first column
     * avoid temporary files before loading to pg
     * load to pg whole file then join non-key fields to v
     */
    '''
    files = glob.glob(fMask)
    if debugmode: print "pgtools.joinPerKeyPg( ", files
    for f in files:
        if debugmode: print f
        # table name
        t = f[:-4]
        # drop table if exists
        if t in listTables(): dropTable(t)

        p = ppt.TxtLoad()
        p.initFile(       inputFile=f
                        , colDelimiter=cDelimiter
                        , hasHeader=0
                        )
        p.debugmode = debugmode
        p.dropTableBefore = 0
        #p.pgClientEncoding = 'UTF8'
        #p.pgClientEncoding = 'cp1250'
        p.queryCreateTable()
        p.createTable()
        p.queryLoadTable()
        p.loadData()

    #if 'temp_jpkg' in listTables(): dropTable('temp_jpkg')


    #of = open('%sjpg-joined.jpg#' % (posb.wai()), 'w')
    #of.write('''
    # JOINED DATA PER FIRST (KEY) COLUMN
    #  NOTE: VALUE CAN BE DIFFERENT, WE DO NOT CARE HERE
    #  TAB EXPECTED AS DELIMITER IN SOURCE FILES
    
    #_
    #''')
    #of.close()

    print "\t", files[0][:-4], files[1][:-4]
    # it is possible to have duplicate columns names when exporting by COPY
    q = """ COPY 
            (
            --select  t.k, ta.c002 as va, tb.c002 as vb, '#' as ta, ta.*, '#' as tb, tb.*
            select  t.k, '#' as ta, ta.*, '#' as tb, tb.*
              from 
                    (       select trim(c001) as k from %s 
                      union select trim(c001) as k from %s) as t 
                    left join %s as ta on t.k = trim(ta.c001)
                    left join %s as tb on t.k = trim(tb.c001)
              order by t.k
            )
            TO '%sjpg-joined.jpg#' with DELIMITER '\t' NULL ''
    """ % (files[0][:-4], files[1][:-4], files[0][:-4], files[1][:-4], posb.wai())
    #print q
    pg.Pg(q)

    # we do not do diff part as in jointt because joinpg should  be more universal
    # meaning : should not expect that value will be always in c002

    for f in files:
        dropTable(f[:-4])

if __name__ == '__main__':
    ''' '''
    joinPerKeyPg(fMask='*.dat', cDelimiter='\t')
    #print dropTable('AMOtb0409')
    #print dropTable('AMOtb0412')
    #print listTables()
    #print dropTable('aaa3')